package com.shaoyuwang;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 设置默认库，并对其库进行插入和查询操作
 */
public class LibDemo {
    public static void main(String[] args) throws SQLException {

        MysqlDataSource db = new MysqlDataSource();
        db.setServerName("localhost");
        db.setPort(3306);
        db.setUser("root");
        db.setPassword("123456");
        db.setDatabaseName("lib_0403");
        db.setUseSSL(false);
        db.setCharacterEncoding("utf-8");
        db.setServerTimezone("Asia/Shanghai");


        // Connection ：连接
        // 类似：打通电话了
        try (Connection c = db.getConnection()) {
            // Statement: 语句 ：要说的话
//            //String sql = "insert into books (name, count, total) values ('小红书', 10, 10)";
//            //String sql = "insert into readers (name) values ('小李')";
//            String sql = "update books set count = count - 1 where bid = 1";
//            try (PreparedStatement ps = c.prepareStatement(sql)) {
//                int n = ps.executeUpdate();
//                System.out.println(n);
//            }
//
//            sql = "insert into records (rid, bid) values (1, 1)";
//            try (PreparedStatement ps = c.prepareStatement(sql)) {
//                int n = ps.executeUpdate();
//                System.out.println(n);
//            }

            String sql = "select reid, rid, bid, borrowed_at from records order by reid";
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        int reid = rs.getInt(1);
                        int rid = rs.getInt("rid");
                        String bid = rs.getString(3);
                        String borrowedAt = rs.getString("borrowed_at");

                        System.out.printf("%d, %d, %s, %s\n", reid, rid, bid, borrowedAt);
                    }
                }
            }
        }
    }
}
